/*table customer*/
select * from TRANGLT9.DB_CUSTOMER.[DBO].TBL_CUSTOMER

select * from TBL_CUSTOMER where EMAIL_ADDR is not null and CUST_TYPE_name <> 'others' order by CIF
ALTER TABLE TBL_CUSTOMER ADD CUST_TYPE_NAME NVARCHAR(50)
SELECT * FROM TBL_CUSTOMER_CONTACT
SELECT b.* FROM  TBL_HR AS B
JOIN TBL_CUSTOMER AS A
ON A.DAO=B.DAO where CUST_TYPE_name <> 'others'

UPDATE TBL_CUSTOMER
SET CUST_TYPE_NAME = B.CUST_TYPE_NAME
FROM TBL_CUSTOMER AS A, TRANGLT9.DB_CUSTOMER.[DBO].TBL_CUSTOMER AS B
WHERE A.CIF=B.CIF

SELECT * FROM TBL_CUSTOMER
SELECT * FROM TRANGLT9.DB_CUSTOMER.[DBO].TBL_CUSTOMER
select * from TBL_CUSTOMER where CUST_TYPE_NAME != 'OTHERS' order by DAO

select * from tbl_hr where dao = '10124'
UPDATE a
SET a.SUB_SEGMENT = b.SUB_SEGMENT
from TBL_CUSTOMER as a, TRANGLT9.DB_CUSTOMER.[DBO].TBL_CUSTOMER as b where a.CIF = b.CIF

alter table tbl_customer
add SUB_SEGMENT nvarchar(100)
/*table relation*/
SELECT b.DAO, a.BIR_OF_REL_CUST FROM TBL_RELATION as a
join TBL_CUSTOMER as b on a.CIF=b.CIF
 where a.BIR_OF_REL_CUST is not null order by BIR_OF_REL_CUST

 select * from TBL_RELATION
/*table hr*/
DROP TABLE TBL_HR


SELECT * INTO TBL_HR_ACL FROM SERVER12.TRANGLT9.[DBO].[USERNAME_REPORT]
select * from tbl_hr_acl
select distinct position into #tam from TBL_HR_ACL

alter table #tam 
add group_id nvarchar(50)

select * from #tam


alter table tbl_hr
add group_id nvarchar(50)

select * from tbl_hr where group_id = 3 and position in ('mbo','sbo')

update c
set group_id= f.group_id
FROM TBL_HR as c join 
(select group_id , username
from  application_group AS A
join TBL_HR_ACL AS B on A.group_name_local=B.position ) as f on f.username=c.[user_name]

update TBL_HR
 set group_id = 4 where group_id = 3 and position in ('mbo','sbo')


SELECT DISTINCT DAO FROM TBL_HR
select * from [dbo].[APPLICATION_GROUP]
select * from TBL_HR order by group_id
/*table hr group count*/
delete from TBL_HR_GROUP_COUNT
select * from TBL_HR_GROUP_COUNT where GROUP_NAME='1.Group Deposit' order by dao
select dao, count(dao) from TBL_HR_GROUP_COUNT group by dao order by DAO

INSERT INTO TBL_HR_GROUP_COUNT
SELECT A.DAO AS DAO,'1.Group Deposit', COUNT(DISTINCT(A.CIF)) AS TOTAL_CUSTOMER,0,0, 'deposit' AS GROUP_CODE
FROM TBL_CUSTOMER AS A, TBL_CM_DEPOSIT AS B
WHERE A.CIF=B.CIF and a.DAO is not null GROUP BY A.DAO

INSERT INTO TBL_HR_GROUP_COUNT
SELECT A.dao_name AS DAO, '1.Group Deposit', COUNT(DISTINCT(c.CIF)) AS TOTAL_CUSTOMER,0,0, 'deposit' AS GROUP_CODE
FROM tbl_hr a, TBL_CUSTOMER b, TBL_CM_DEPOSIT c, TBL_BRANCH d
 where a.group_id = 3
 and b.CIF=c.CIF
 and a.branch_code_sme = d.BRANCH_CODE_SME
 and b.BRANCH_ID = d.BRANCH_ID
 group by a.dao, a.dao_name, a.group_id, d.BRANCH_ID
 order by d.BRANCH_ID

INSERT INTO TBL_HR_GROUP_COUNT
SELECT A.dao_name AS DAO, '1.Group Deposit', COUNT(DISTINCT(c.CIF)) AS TOTAL_CUSTOMER,0,0, 'deposit' AS GROUP_CODE
FROM tbl_hr a, TBL_CUSTOMER b, TBL_CM_DEPOSIT c, TBL_BRANCH d
where a.group_id = 2
and b.CIF=c.CIF
and a.branch_code_sme = d.REGION
and b.BRANCH_ID = d.BRANCH_ID
group by a.dao, a.dao_name, a.group_id

INSERT INTO TBL_HR_GROUP_COUNT
SELECT A.dao_name AS DAO, '1.Group Deposit', (select COUNT(distinct CIF) from TBL_CM_DEPOSIT) AS TOTAL_CUSTOMER,0,0, 'deposit' AS GROUP_CODE
FROM tbl_hr a
where a.group_id = 1


select * from TBL_CM_DEPOSIT order by CIF
select * from TBL_HR_GROUP_COUNT where GROUP_NAME='1.Group Deposit'

update TBL_HR_GROUP_COUNT
set NEW_BY_CIF= f.countcif
FROM TBL_HR_GROUP_COUNT as c join 
(select dao, count (DISTINCT(A.CIF)) as countcif 
from  TBL_CUSTOMER AS A
join TBL_CM_DEPOSIT AS B on A.CIF=B.CIF 
where a.DAO is not null and a.CUS_OPEN_DATE > '20160521' 
GROUP BY A.DAO ) as f on f.dao=c.dao

INSERT INTO TBL_HR_GROUP_COUNT
SELECT A.DAO AS DAO,'2.GROUP LOAN', COUNT(DISTINCT(A.CIF)) AS TOTAL_CUSTOMER,0,0, 'loan' AS GROUP_CODE
FROM TBL_CUSTOMER AS A, TBL_CM_LOAN AS B
WHERE A.CIF=B.CIF AND A.DAO IS NOT NULL AND B.CHANNEL<>'DSA' GROUP BY A.DAO


INSERT INTO TBL_HR_GROUP_COUNT
SELECT A.dao_name AS DAO, '2.GROUP LOAN', COUNT(DISTINCT(c.CIF)) AS TOTAL_CUSTOMER,0,0, 'loan' AS GROUP_CODE
FROM tbl_hr a, TBL_CUSTOMER b, TBL_CM_LOAN c, TBL_BRANCH d
 where a.group_id = 3
 and b.CIF=c.CIF
 and a.branch_code_sme = d.BRANCH_CODE_SME
 and b.BRANCH_ID = d.BRANCH_ID
 group by a.dao, a.dao_name, a.group_id, d.BRANCH_ID
 order by d.BRANCH_ID

INSERT INTO TBL_HR_GROUP_COUNT
SELECT A.dao_name AS DAO, '2.GROUP LOAN', COUNT(DISTINCT(c.CIF)) AS TOTAL_CUSTOMER,0,0, 'loan' AS GROUP_CODE
FROM tbl_hr a, TBL_CUSTOMER b, TBL_CM_LOAN c, TBL_BRANCH d
where a.group_id = 2
and b.CIF=c.CIF
and a.branch_code_sme = d.REGION
and b.BRANCH_ID = d.BRANCH_ID
group by a.dao, a.dao_name, a.group_id

INSERT INTO TBL_HR_GROUP_COUNT
SELECT A.dao_name AS DAO, '2.GROUP LOAN', (select COUNT(distinct CIF) from TBL_CM_LOAN) AS TOTAL_CUSTOMER,0,0, 'loan' AS GROUP_CODE
FROM tbl_hr a
where a.group_id = 1

update TBL_HR_GROUP_COUNT
set NEW_BY_CIF= f.countcif
FROM TBL_HR_GROUP_COUNT as c join 
(select dao, count (DISTINCT(A.CIF)) as countcif 
from  TBL_CUSTOMER AS A
join TBL_CM_LOAN AS B on A.CIF=B.CIF 
where a.DAO is not null and a.CUS_OPEN_DATE > (SELECT DATEADD(m,-1,GETDATE()) )
GROUP BY A.DAO ) as f on f.dao=c.dao where c.GROUP_CODE='loan'

UPDATE TBL_HR_GROUP_COUNT
SET NEW_BY_RULE= F.COUNTCIF
FROM TBL_HR_GROUP_COUNT AS C JOIN 
(SELECT A.DAO, COUNT (DISTINCT(A.CIF)) AS COUNTCIF --into #tam
FROM  TBL_CUSTOMER AS A
JOIN TBL_NEW_PR_LOAN AS B ON A.CIF=B.CIF 
WHERE A.DAO IS NOT NULL 
GROUP BY A.DAO ) AS F ON F.DAO=C.DAO WHERE C.GROUP_CODE='loan'

INSERT INTO TBL_HR_GROUP_COUNT
SELECT A.DAO AS DAO,'3.GROUP CARD', COUNT(DISTINCT(A.CIF)) AS TOTAL_CUSTOMER,0,0, 'card' AS GROUP_CODE
FROM TBL_CUSTOMER AS A, TBL_CM_CARD AS B
WHERE A.CIF=B.CIF AND A.DAO IS NOT NULL GROUP BY A.DAO

INSERT INTO TBL_HR_GROUP_COUNT
SELECT A.dao_name AS DAO, '3.GROUP CARD', COUNT(DISTINCT(c.CIF)) AS TOTAL_CUSTOMER,0,0, 'card' AS GROUP_CODE
FROM tbl_hr a, TBL_CUSTOMER b, TBL_CM_CARD c, TBL_BRANCH d
 where a.group_id = 3
 and b.CIF=c.CIF
 and a.branch_code_sme = d.BRANCH_CODE_SME
 and b.BRANCH_ID = d.BRANCH_ID
 group by a.dao, a.dao_name, a.group_id, d.BRANCH_ID
 order by d.BRANCH_ID

INSERT INTO TBL_HR_GROUP_COUNT
SELECT A.dao_name AS DAO, '3.GROUP CARD', COUNT(DISTINCT(c.CIF)) AS TOTAL_CUSTOMER,0,0, 'card' AS GROUP_CODE
FROM tbl_hr a, TBL_CUSTOMER b, TBL_CM_CARD c, TBL_BRANCH d
where a.group_id = 2
and b.CIF=c.CIF
and a.branch_code_sme = d.REGION
and b.BRANCH_ID = d.BRANCH_ID
group by a.dao, a.dao_name, a.group_id

INSERT INTO TBL_HR_GROUP_COUNT
SELECT A.dao_name AS DAO, '3.GROUP CARD', (select COUNT(distinct CIF) from TBL_CM_CARD) AS TOTAL_CUSTOMER,0,0, 'card' AS GROUP_CODE
FROM tbl_hr a
where a.group_id = 1

update TBL_HR_GROUP_COUNT
set NEW_BY_CIF= f.countcif
FROM TBL_HR_GROUP_COUNT as c join 
(select dao, count (DISTINCT(A.CIF)) as countcif 
from  TBL_CUSTOMER AS A
join TBL_CM_CARD AS B on A.CIF=B.CIF 
where a.DAO is not null and a.CUS_OPEN_DATE > (SELECT DATEADD(m,-1,GETDATE()))
GROUP BY A.DAO ) as f on f.dao=c.dao where c.GROUP_CODE='card'

INSERT INTO TBL_HR_GROUP_COUNT
SELECT A.DAO AS DAO,'4.GROUP POS', COUNT(DISTINCT(A.CIF)) AS TOTAL_CUSTOMER,0,0, 'pos' AS GROUP_CODE
FROM TBL_CUSTOMER AS A, TBL_CM_POS AS B
WHERE A.CIF=B.CIF AND A.DAO IS NOT NULL GROUP BY A.DAO


INSERT INTO TBL_HR_GROUP_COUNT
SELECT A.dao_name AS DAO, '4.GROUP POS', COUNT(DISTINCT(c.CIF)) AS TOTAL_CUSTOMER,0,0, 'pos' AS GROUP_CODE
FROM tbl_hr a, TBL_CUSTOMER b, TBL_CM_POS c, TBL_BRANCH d
 where a.group_id = 3
 and b.CIF=c.CIF
 and a.branch_code_sme = d.BRANCH_CODE_SME
 and b.BRANCH_ID = d.BRANCH_ID
 group by a.dao, a.dao_name, a.group_id, d.BRANCH_ID
 order by d.BRANCH_ID

INSERT INTO TBL_HR_GROUP_COUNT
SELECT A.dao_name AS DAO, '4.GROUP POS', COUNT(DISTINCT(c.CIF)) AS TOTAL_CUSTOMER,0,0, 'pos' AS GROUP_CODE
FROM tbl_hr a, TBL_CUSTOMER b, TBL_CM_POS c, TBL_BRANCH d
where a.group_id = 2
and b.CIF=c.CIF
and a.branch_code_sme = d.REGION
and b.BRANCH_ID = d.BRANCH_ID
group by a.dao, a.dao_name, a.group_id

INSERT INTO TBL_HR_GROUP_COUNT
SELECT A.dao_name AS DAO, '4.GROUP POS', (select COUNT(distinct CIF) from TBL_CM_POS) AS TOTAL_CUSTOMER,0,0, 'pos' AS GROUP_CODE
FROM tbl_hr a
where a.group_id = 1

update TBL_HR_GROUP_COUNT
set NEW_BY_CIF= f.countcif
FROM TBL_HR_GROUP_COUNT as c join 
(select dao, count (DISTINCT(A.CIF)) as countcif 
from  TBL_CUSTOMER AS A
join TBL_CM_POS AS B on A.CIF=B.CIF 
where a.DAO is not null and a.CUS_OPEN_DATE > (SELECT DATEADD(m,-1,GETDATE()) )
GROUP BY A.DAO ) as f on f.dao=c.dao where c.GROUP_CODE='pos'

INSERT INTO TBL_HR_GROUP_COUNT
SELECT A.DAO AS DAO,'5.GROUP I2B', COUNT(DISTINCT(A.CIF)) AS TOTAL_CUSTOMER,0,0, 'i2b' AS GROUP_CODE
FROM TBL_CUSTOMER AS A, TBL_CM_I2B AS B
WHERE A.CIF=B.CIF AND A.DAO IS NOT NULL GROUP BY A.DAO

INSERT INTO TBL_HR_GROUP_COUNT
SELECT A.dao_name AS DAO, '5.GROUP I2B', COUNT(DISTINCT(c.CIF)) AS TOTAL_CUSTOMER,0,0, 'i2b' AS GROUP_CODE
FROM tbl_hr a, TBL_CUSTOMER b, TBL_CM_I2B c, TBL_BRANCH d
 where a.group_id = 3
 and b.CIF=c.CIF
 and a.branch_code_sme = d.BRANCH_CODE_SME
 and b.BRANCH_ID = d.BRANCH_ID
 group by a.dao, a.dao_name, a.group_id, d.BRANCH_ID
 order by d.BRANCH_ID

INSERT INTO TBL_HR_GROUP_COUNT
SELECT A.dao_name AS DAO, '5.GROUP I2B', COUNT(DISTINCT(c.CIF)) AS TOTAL_CUSTOMER,0,0, 'i2b' AS GROUP_CODE
FROM tbl_hr a, TBL_CUSTOMER b, TBL_CM_I2B c, TBL_BRANCH d
where a.group_id = 2
and b.CIF=c.CIF
and a.branch_code_sme = d.REGION
and b.BRANCH_ID = d.BRANCH_ID
group by a.dao, a.dao_name, a.group_id

INSERT INTO TBL_HR_GROUP_COUNT
SELECT A.dao_name AS DAO, '5.GROUP I2B', (select COUNT(distinct CIF) from TBL_CM_I2B) AS TOTAL_CUSTOMER,0,0, 'i2b' AS GROUP_CODE
FROM tbl_hr a
where a.group_id = 1


update TBL_HR_GROUP_COUNT
set NEW_BY_CIF= f.countcif
FROM TBL_HR_GROUP_COUNT as c join 
(select dao, count (DISTINCT(A.CIF)) as countcif 
from  TBL_CUSTOMER AS A
join TBL_CM_I2B AS B on A.CIF=B.CIF 
where a.DAO is not null and a.CUS_OPEN_DATE > (SELECT DATEADD(m,-1,GETDATE()) )
GROUP BY A.DAO ) as f on f.dao=c.dao where c.GROUP_CODE='i2b'

INSERT TBL_HR_GROUP_COUNT
  SELECT DAO, '6. GROUP TRADE', COUNT(CIF), 0 , 0, 'trade' FROM TBL_CM_TRADE where dao is not null
  GROUP BY DAO

INSERT INTO TBL_HR_GROUP_COUNT
SELECT A.dao_name AS DAO, '6.GROUP TRADE', COUNT(DISTINCT(c.CIF)) AS TOTAL_CUSTOMER,0,0, 'trade' AS GROUP_CODE
FROM tbl_hr a, TBL_CUSTOMER b, TBL_CM_TRADE c, TBL_BRANCH d
 where a.group_id = 3
 and b.CIF=c.CIF
 and a.branch_code_sme = d.BRANCH_CODE_SME
 and b.BRANCH_ID = d.BRANCH_ID
 group by a.dao, a.dao_name, a.group_id, d.BRANCH_ID
 order by d.BRANCH_ID

INSERT INTO TBL_HR_GROUP_COUNT
SELECT A.dao_name AS DAO, '6.GROUP TRADE', COUNT(DISTINCT(c.CIF)) AS TOTAL_CUSTOMER,0,0, 'trade' AS GROUP_CODE
FROM tbl_hr a, TBL_CUSTOMER b, TBL_CM_TRADE c, TBL_BRANCH d
where a.group_id = 2
and b.CIF=c.CIF
and a.branch_code_sme = d.REGION
and b.BRANCH_ID = d.BRANCH_ID
group by a.dao, a.dao_name, a.group_id

INSERT INTO TBL_HR_GROUP_COUNT
SELECT A.dao_name AS DAO, '6.GROUP TRADE', (select COUNT(distinct CIF) from TBL_CM_TRADE) AS TOTAL_CUSTOMER,0,0, 'trade' AS GROUP_CODE
FROM tbl_hr a
where a.group_id = 1

  update  b
  set NEW_BY_RULE =a.counts
  from (select count(cif) as counts, dao from TBL_CM_TRADE where new_cust_status = 1 group by DAO) as a, TBL_HR_GROUP_COUNT b
  where a.DAO=b.DAO and b.GROUP_CODE = 'trade'


INSERT INTO TBL_HR_GROUP_COUNT
SELECT A.DAO AS DAO,'7.Group Chuoi', COUNT(DISTINCT(A.CIF)) AS TOTAL_CUSTOMER,0,0, 'chuoi' AS GROUP_CODE
FROM TBL_CUSTOMER AS A, TBL_TT_CHUOI AS B
WHERE A.CIF=B.cust_id and a.DAO is not null GROUP BY A.DAO

INSERT INTO TBL_HR_GROUP_COUNT
SELECT A.dao_name AS DAO, '7.Group Chuoi', COUNT(DISTINCT(c.cust_id)) AS TOTAL_CUSTOMER,0,0, 'chuoi' AS GROUP_CODE
FROM tbl_hr a, TBL_CUSTOMER b, TBL_TT_CHUOI c, TBL_BRANCH d
 where a.group_id = 3
 and b.CIF=c.cust_id
 and a.branch_code_sme = d.BRANCH_CODE_SME
 and b.BRANCH_ID = d.BRANCH_ID
 group by a.dao, a.dao_name, a.group_id, d.BRANCH_ID
 order by d.BRANCH_ID

INSERT INTO TBL_HR_GROUP_COUNT
SELECT A.dao_name AS DAO, '7.Group Chuoi', COUNT(DISTINCT(c.cust_id)) AS TOTAL_CUSTOMER,0,0, 'chuoi' AS GROUP_CODE
FROM tbl_hr a, TBL_CUSTOMER b, TBL_TT_CHUOI c, TBL_BRANCH d
where a.group_id = 2
and b.CIF=c.cust_id
and a.branch_code_sme = d.REGION
and b.BRANCH_ID = d.BRANCH_ID
group by a.dao, a.dao_name, a.group_id

INSERT INTO TBL_HR_GROUP_COUNT
SELECT A.dao_name AS DAO, '7.Group Chuoi', (select COUNT(distinct cust_id) from TBL_TT_CHUOI) AS TOTAL_CUSTOMER,0,0, 'chuoi' AS GROUP_CODE
FROM tbl_hr a
where a.group_id = 1

delete from TBL_HR_GROUP_COUNT where GROUP_CODE = 'chuoi'

select * 
from TBL_HR_GROUP_COUNT a,  tbl_hr b
where NEW_BY_RULE > 0 and GROUP_CODE = 'trade' and a.DAO=b.dao

/*table deposit*/
 select * from TBL_CM_DEPOSIT where DAO_CIF='10045'
select * from TBL_CM_DEPOSIT
/* Table Loan*/
SELECT * INTO TBL_CM_LOAN FROM TRANGLT9.DB_CUSTOMER.[DBO].[TBL_CM_LOAN]

select distinct(CHANNEL) from TBL_CM_LOAN

SELECT DISTINCT NHOM_SP_LOAN FROM TBL_CM_LOAN

update TBL_CM_LOAN
set CHUONG_TRINH_SP='00' where CHUONG_TRINH_SP is null

UPDATE TBL_CM_LOAN
SET CT_UUDAI_VAY='00' WHERE CT_UUDAI_VAY IS NULL

/*table i2b*/
SELECT * INTO TBL_CM_I2B FROM TRANGLT9.DB_CUSTOMER.[DBO].[TBL_CM_I2B]
SELECT * INTO TBL_CM_I2B_DETAIL FROM TRANGLT9.DB_CUSTOMER.[DBO].[I2B_ALL_DAY_RAW]

select * from TBL_CM_I2B where CIF='1186601'
select * from TBL_CM_I2B_DETAIL

SELECT c.*
FROM TBL_CUSTOMER AS A
JOIN TBL_CM_I2B AS B ON A.CIF=B.CIF
JOIN TBL_HR AS C ON A.DAO = C.DAO

/*table pos*/
drop table tbl_cm_pos
SELECT * INTO TBL_CM_POS FROM TRANGLT9.DB_CUSTOMER.[DBO].[TBL_CM_POS]

select b.* from TBL_CM_POS as a 
join TBL_CUSTOMER as b
on b.CIF = a.cif order by CUS_OPEN_DATE

SELECT b.*
FROM TBL_CUSTOMER AS A
JOIN TBL_CM_POS AS B ON A.CIF=B.CIF
JOIN TBL_HR AS C ON A.DAO = C.DAO where c.DAO='10045'


/*table alert*/

drop table TBL_ALERT_BAD_BANK
drop table TBL_ALERT_LOAN_MATDT

select * into TBL_ALERT_BAD_BANK from TrangLT9.DB_CUSTOMER.[dbo].[TBL_ALERT_BAD_BANK]

select * into TBL_ALERT_LOAN_MATDT from TrangLT9.DB_CUSTOMER.[dbo].[TBL_ALERT_LOAN_MATDT]
 
select * into [TBL_ALERT_DEPOSIT_MATDT] 
from TrangLT9.DB_CUSTOMER.[dbo].[TBL_ALERT_DEPOSIT_MATDT]

select * from TBL_ALERT_BAD_BANK

select * from TBL_ALERT_LOAN_MATDT

select * from TBL_ALERT_CURRENT_ACCOUNT 

select * from TBL_ALERT_DEPOSIT_MATDT
select * from TBL_HR where dao = '1864'

/*table top 10*/
select * from TrangLT9.db_customer.[dbo].[TOI_201605]

DROP TABLE TBL_TOI

select * into TBL_TOI from TrangLT9.db_customer.[dbo].[TBL_TOI]

select * from TBL_TOI order by dao_cif

/*table new lending*/
select * from TrangLT9.db_customer.[dbo].[TBL_NEW_PR_LOAN]

select * into TBL_NEW_PR_LOAN from TrangLT9.db_customer.[dbo].[TBL_NEW_PR_LOAN]

select * from TBL_NEW_PR_LOAN order by dao

select distinct INDUSTRY_NAME,VPB_INDUSTRY from TBL_CUSTOMER group by INDUSTRY_NAME,VPB_INDUSTRY order by VPB_INDUSTRY


/* table Trade*/

SELECT * FROM TRANGLT9.DB_CUSTOMER.[DBO].[TBL_TRADE_CT_UUDAI]

SELECT * FROM TRANGLT9.DB_CUSTOMER.[DBO].[TBL_TRADE_DAILY_CT_UUDAI]

SELECT * FROM TRANGLT9.DB_CUSTOMER.[DBO].[TBL_DU_NO_LC_DAILY] ORDER BY CUST_ID

SELECT * FROM TRANGLT9.DB_CUSTOMER.[DBO].[TBL_DU_TRADE_LOAN_DAILY]

SELECT * FROM TRANGLT9.DB_CUSTOMER.[DBO].[TBL_LC_BALANCE]

SELECT * FROM TRANGLT9.DB_CUSTOMER.[DBO].[AUTO_REPORT_LC_WEB]

/* table account planning*/

SELECT * FROM TBL_TONG_HOP_ACCOUNT_PLANNING ORDER BY CIF


DELETE FROM TBL_TONG_HOP_ACCOUNT_PLANNING WHERE CIF IS NULL

SELECT CIF, 
[KHÁCH HÀNG] AS CUST_NAME,
[LĨNH VỰC KINH DOANH] AS INDUSTRY,
[PHƯƠNG THỨC THANH TOÁN] AS PAYMENT_METHOD,
[TÌNH HÌNH KHAI THÁC] AS TINH_HINH_KHAI_THAC,
[DỰ KIẾN 2016] AS PLAN2016,
[kẾ HOẠCH HÀNH ĐỘNG (T6/2016)] AS ACTION_PLAN_T62016,
[WALLET SHARE] AS WALLET_SHARE,
[TỶ LỆ SỬ DỤNG HẠN MỨC] AS TY_LE_SU_DUNG_HAN_MUC,
[VƯỚNG TRONG SỬ DỤNG HẠN MỨC] AS VUONG_MAC,
[CÁC SẢN PHẨM ĐANG SỬ DỤNG TẠI VP] AS SP_DANG_SU_DUNG,
[SẢN PHẨM CÓ THỂ BÁN CHÉO] AS SP_BAN_CHEO,
[KẾ HOẠCH HỖ TRỢ ĐVKD] AS KH_HO_TRO,
[DỰ KIẾN THU PHÍ CỦA KH TRONG NĂM 2016] AS THU_PHI2016
INTO TBL_ACCOUNT_PLANNING
FROM TBL_TONG_HOP_ACCOUNT_PLANNING


SELECT c.* FROM TBL_ACCOUNT_PLANNING AS A
JOIN TBL_CUSTOMER AS B ON A.CIF = B.CIF
JOIN TBL_HR AS C ON B.DAO=C.DAO
where b.CIF='232136'

select * from TBL_ACCOUNT_PLANNING order by dao_cif

select * from tbl_hr where dao = '4946'
SELECT * FROM TBL_ACCOUNT_PLANNING where cif = '1234927'

ALTER TABLE TBL_ACCOUNT_PLANNING 
ADD DAO_CIF NVARCHAR(100)

update TBL_ACCOUNT_PLANNING
set cif = '232136'
where cif = ' 232136'
UPDATE A
SET A.DAO_CIF = B.DAO
FROM TBL_ACCOUNT_PLANNING AS A, TBL_CUSTOMER AS B ,TBL_HR AS C
WHERE A.CIF=B.CIF AND B.DAO=C.DAO

SELECT * FROM TBL_HR

drop table Data_Account_Planning_Temp
 select * from [dbo].[Data_Account_Planning_Temp]
 update Data_Account_Planning_Temp set F15=0 where [Tháng 12] is null
 select * from TBL_DATA_ACCOUNT_PLANNING where cif='775048'

 delete from TBL_DATA_ACCOUNT_PLANNING
 insert into TBL_DATA_ACCOUNT_PLANNING
 select a.F1, 0, 0,0,0,0,0,0,0,0,0,0,0,0, b.CIF, null, null, 0, a.f15,a.[2016]
 from Data_Account_Planning_Temp as a, TBL_ACCOUNT_PLANNING as b

 update TBL_DATA_ACCOUNT_PLANNING
 set data_type=1
 where name like '%bình quân%'
  

  select name, data_type, order_num, cif , [status] from TBL_DATA_ACCOUNT_PLANNING order by cif

  select * from TBL_DATA_ACCOUNT_PLANNING where cif='1234927'
/*TABLE DAO COUNT INFO*/

delete from TBL_DAO_INFO
INSERT INTO TBL_DAO_INFO
SELECT B.DAO ,COUNT(CIF) AS Total_Customer, 0,0,0
FROM  TBL_CUSTOMER AS B
WHERE DAO IS NOT NULL
 GROUP BY  B.DAO

IF EXISTS
      (SELECT *
         FROM tempdb.DBO.SYSOBJECTS O
        WHERE o.xtype IN ('U') AND o.name = '#tam')
        DROP TABLE #tam

SELECT B.DAO ,COUNT(CIF) AS Total_Customer into #tam
FROM  TBL_CUSTOMER AS B
WHERE DAO IS NOT NULL and CUS_OPEN_DATE > (SELECT DATEADD(m,-1,GETDATE()) )
 GROUP BY  B.DAO

 UPDATE A
SET A.NEW_CUSTOMER = B.TOTAL_CUSTOMER
FROM TBL_DAO_INFO AS A, #TAM AS B
WHERE A.DAO=B.DAO


 select * from #tam


 select * from TBL_HR where group_id='2'



 delete from [DBCustomer].dbo.TBL_DAO_INFO
insert into [DBCustomer].dbo.TBL_DAO_INFO
select * from TBL_DAO_INFO

EXEC('IF EXISTS
      (SELECT *
         FROM [SERVER12].[SMECustomer360].DBO.SYSOBJECTS O
        WHERE O.XTYPE IN (''U'') AND O.NAME = ''TBL_CUSTOMER'')
        delete from [SERVER12].[SMECustomer360].dbo.TBL_CUSTOMER')
 
insert [SERVER12].[SMECustomer360].dbo.TBL_CUSTOMER
select * from TBL_CUSTOMER

select * from TrangLT9.[DB_CUSTOMER].[dbo].[TBL_CM_CARD_TONG_HOP]